package com.ops.service;

import com.ops.entity.OpsTask;
import com.ops.repository.OpsTaskRepository;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Service
public class OpsTaskService {
    
    @Autowired
    private OpsTaskRepository opsTaskRepository;
    
    /**
     * 导入Excel文件中的运维任务数据
     * 
     * @param file 上传的Excel文件
     * @param hospitalId 医院ID
     * @param demandYear 需求年份
     * @param reportUuid 报告UUID
     * @return 成功导入的记录数
     * @throws Exception 导入过程中可能出现的异常
     */
    @Transactional
    public int importExcel(MultipartFile file, String hospitalId, String demandYear, String reportUuid) throws Exception {
        // 先删除已存在的数据，使用 deleteAll 确保事务同步
        List<OpsTask> existingTasks = opsTaskRepository.findByReportUuid(reportUuid);
        if (!existingTasks.isEmpty()) {
            opsTaskRepository.deleteAll(existingTasks);
            opsTaskRepository.flush(); // 强制刷新，确保删除操作立即生效
        }

        List<OpsTask> tasksToSave = new ArrayList<>();

        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过表头

                OpsTask task = new OpsTask();
                
                // 设置唯一索引字段
                String demandId = getCellValueAsString(row.getCell(0));        // 需求ID
                // 需求 ID 为空的跳过
                if (demandId == null || demandId.isEmpty()) continue;
                
                // 设置所有字段
                task.setHospitalId(hospitalId);
                task.setDemandYear(demandYear);
                task.setDemandId(demandId);
                task.setReportUuid(reportUuid);
                task.setPriorityLevel(getCellValueAsString(row.getCell(1)));     // 优先级
                task.setProjectName(getCellValueAsString(row.getCell(2)));       // 项目名称
                task.setDemandName(getCellValueAsString(row.getCell(3)));        // 需求名称
                task.setDemandCreator(getCellValueAsString(row.getCell(4)));     // 需求发起人
                task.setCurrentHandler(getCellValueAsString(row.getCell(5)));    // 需求当前指派人
                
                // 处理需求日期
                String demandDateStr = getCellValueAsString(row.getCell(6));
                if (demandDateStr != null && !demandDateStr.isEmpty()) {
                    task.setDemandDate(parseDateTime(demandDateStr));
                }
                
                task.setTaskStatus(getCellValueAsString(row.getCell(7)));        // 需求状态
                String taskId = getCellValueAsString(row.getCell(8));         // 任务ID
                task.setTaskId(taskId);
                task.setTaskName(getCellValueAsString(row.getCell(9)));         // 任务名称
                task.setCurrentAssignee(getCellValueAsString(row.getCell(10)));  // 当前指派人
                task.setTaskStatus(getCellValueAsString(row.getCell(11)));       // 任务状态
                
                // 处理计划完成时间
                String planCompletionTimeStr = getCellValueAsString(row.getCell(12));
                if (planCompletionTimeStr != null && !planCompletionTimeStr.isEmpty()) {
                    task.setPlanCompletionTime(parseDateTime(planCompletionTimeStr));
                }

                task.setTaskCompleted(getCellValueAsString(row.getCell(13)));    // 任务完成者

                // 处理实际完成时间
                String actualCompletionTimeStr = getCellValueAsString(row.getCell(14));
                if (actualCompletionTimeStr != null && !actualCompletionTimeStr.isEmpty()) {
                    task.setActualCompletionTime(parseDateTime(actualCompletionTimeStr));
                }
                
                task.setEstimatedWorkload(getNumericCellValue(row.getCell(15))); // 预估工作量
                task.setActualWorkload(getNumericCellValue(row.getCell(16)));    // 实际工作量
                task.setRegion(getCellValueAsString(row.getCell(17)));           // 区域

                // 根据唯一索引查找现有记录
//                Optional<OpsTask> existingTask = opsTaskRepository.findByReportUuidAndHospitalIdAndDemandYearAndDemandId(
//                        reportUuid,hospitalId, demandYear, demandId
//                );
//                // 如果存在记录，则更新ID
//                existingTask.ifPresent(opsTask -> task.setId(opsTask.getId()));
//
                tasksToSave.add(task);
            }
        }
        
        // 批量保存新数据
        if (!tasksToSave.isEmpty()) {
            opsTaskRepository.saveAll(tasksToSave);
            opsTaskRepository.flush(); // 确保保存操作立即生效
        }
        
        // 返回更新的记录数
        return tasksToSave.size();
    }
    
    /**
     * 解析日期时间字符串为LocalDateTime对象
     * 
     * @param dateStr 日期时间字符串
     * @return 解析后的LocalDateTime对象，解析失败时返回null
     */
    private LocalDateTime parseDateTime(String dateStr) {
        try {
            if (dateStr.contains(".")) {
                dateStr = dateStr.substring(0, dateStr.indexOf("."));
            }
            
            DateTimeFormatter formatter;
            if (dateStr.length() > 16) {
                formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            } else {
                formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
            }
            return LocalDateTime.parse(dateStr, formatter);
        } catch (Exception e) {
            return null;
        }
    }
    
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue()
                            .format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                }
                // 对于数值，保留两位小数
                return String.format("%.2f", cell.getNumericCellValue());
            default:
                return null;
        }
    }
    
    private Double getNumericCellValue(Cell cell) {
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        } else if (cell.getCellType() == CellType.STRING) {
            try {
                return Double.parseDouble(cell.getStringCellValue().trim());
            } catch (NumberFormatException e) {
                return null;
            }
        }
        return null;
    }
    
    public Page<OpsTask> findTopTasks(String reportUuid,String hospitalId, String demandYear, int page, int size) {
        return opsTaskRepository.findTopTasksByHospitalAndYear(
            reportUuid,
            hospitalId, 
            demandYear, 
            PageRequest.of(page, size)
        );
    }
    
    public List<Map<String, Object>> getMonthlyDemandStats(String reportUuid, String hospitalId, String demandYear) {
        List<Object[]> stats = opsTaskRepository.getMonthlyDemandStats(reportUuid);
        List<Map<String, Object>> result = new ArrayList<>();
        
        // 初始化12个月的数据
        for (int i = 1; i <= 12; i++) {
            Map<String, Object> monthData = new HashMap<>();
            monthData.put("month", i);
            monthData.put("count", 0);
            result.add(monthData);
        }
        
        // 填充实际数据
        for (Object[] stat : stats) {
            int month = ((Number) stat[0]).intValue();
            long count = ((Number) stat[1]).longValue();
            result.get(month - 1).put("count", count);
        }
        
        return result;
    }
    
    public List<Map<String, Object>> getMonthlyWorkloadStats(String reportUuid,String hospitalId, String demandYear) {
        List<Object[]> stats = opsTaskRepository.getMonthlyWorkloadStats(reportUuid);
        List<Map<String, Object>> result = new ArrayList<>();
        
        // 初始化12个月的数据
        for (int i = 1; i <= 12; i++) {
            Map<String, Object> monthData = new HashMap<>();
            monthData.put("month", i);
            monthData.put("workload", 0.0);
            result.add(monthData);
        }
        
        // 填充实际数据
        for (Object[] stat : stats) {
            int month = ((Number) stat[0]).intValue();
            double workload = stat[1] != null ? ((Number) stat[1]).doubleValue() : 0.0;
            result.get(month - 1).put("workload", workload);
        }
        
        return result;
    }
    
    public Map<String, Object> getTaskStats(String reportUuid) {
        Map<String, Object> stats = new HashMap<>();
        
        // 查询所有任务
        List<OpsTask> tasks = opsTaskRepository.findByReportUuid(reportUuid);
        
        // 计算总数
        stats.put("totalRequests", tasks.size());
        
        // 计算总工时
        double totalHours = tasks.stream()
            .filter(task -> task.getActualWorkload() != null)
            .mapToDouble(OpsTask::getActualWorkload)
            .sum();
        stats.put("totalOpsHours", totalHours);
        
        return stats;
    }
}